package sy.service.impl;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import sy.dao.*;
import sy.model.*;
import sy.pageModel.SessionInfo;
import sy.pageModel.Transport;
import sy.pageModel.DataGrid;
import sy.pageModel.PageHelper;
import sy.service.TransportServiceI;
import sy.util.EmailUtil;
import sy.util.excel.ExcelUtils;
import sy.util.excel.ExcelWorkSheet;
import sy.util.excel.JsGridReportBase;
import sy.util.excel.TableData;

import javax.mail.MessagingException;
import javax.mail.internet.AddressException;

@Service
public class TransportServiceImpl implements TransportServiceI {

	private static final Logger logger = LoggerFactory.getLogger(TransportServiceImpl.class);

	@Autowired
	private TransportDaoI transportDao;
	@Autowired
	private TransportDetailDaoI transportDetailDao;
	@Autowired
	private StorerDaoI storerDao;
	@Autowired
	private ProductDaoI productDao;
	@Autowired
	private TraceDaoI traceDao;

	@Override
	public DataGrid dataGrid(Transport transport, PageHelper ph,SessionInfo sessionInfo) {
		DataGrid dg = new DataGrid();
		List<Transport> transports = new ArrayList<Transport>();
		Map<String, Object> params = new HashMap<String, Object>();
		String hql = " from Ttransport t ";
		List<Ttransport> l = transportDao.find(hql + whereHql(transport, params,sessionInfo) + orderHql(ph), params, ph.getPage(), ph.getRows());
		if(l!=null && l.size()>0){
			for(Ttransport tr : l){
				Transport t = new Transport();
				BeanUtils.copyProperties(tr,t);
				if(tr.getOwner()!=null){
					t.setOwnerId(tr.getOwner().getId());
					t.setOwnerName(tr.getOwner().getName());
				}
				if(tr.getFactory()!=null){
					t.setFactoryId(tr.getFactory().getId());
					t.setFactoryName(tr.getFactory().getName());
				}
				if(tr.getCarrier()!=null){
					t.setCarrierId(tr.getCarrier().getId());
					t.setCarrierName(tr.getCarrier().getName());
				}
				transports.add(t);
			}
		}

		dg.setRows(transports);
		dg.setTotal(transportDao.count("select count(*) " + hql + whereHql(transport, params,sessionInfo), params));
		return dg;
	}

	private String whereHql(Transport transport, Map<String, Object> params,SessionInfo sessionInfo) {
		String hql = "";
		if (transport != null) {
			hql += " where 1=1 ";
			hql = spiltcodes(transport, hql);
			//根据创建日期筛选订单
			if(transport.getCreateTimeStart()!=null){
				hql += " and t.createTime >= :createTimeStart";
				params.put("createTimeStart", transport.getCreateTimeStart());
			}
			if(transport.getCreateTimeEnd()!=null){
				hql += " and t.createTime <= :createTimeEnd";
				params.put("createTimeEnd", transport.getCreateTimeEnd());
			}

			//根据订单类型筛选订单
			if(transport.getType()!=null && !transport.getType().equals("")){
				hql += " and t.type = :type";
				params.put("type",transport.getType());
			}

			if(sessionInfo.getCarrierCodes()!=null){
				String enddelivery="";
				String carrierCodes=sessionInfo.getCarrierCodes();
				for (String delivery : carrierCodes.split(",")) {
					if (delivery != null && !delivery.equalsIgnoreCase("")) {
						enddelivery+="'"+delivery+"',";
					}
				}
				enddelivery=enddelivery.substring(0,enddelivery.length()-1);
				hql+=" and (t.carrier.code in ("+enddelivery+")";
			}
			//发货工厂
			if(sessionInfo.getFactoryCodes()!=null){
				String enddelivery="";
				String xdcCodes=sessionInfo.getFactoryCodes();
				for (String delivery : xdcCodes.split(",")) {
					if (delivery != null && !delivery.equalsIgnoreCase("")) {
						enddelivery+="'"+delivery+"',";
					}
				}
				enddelivery=enddelivery.substring(0,enddelivery.length()-1);
				if(sessionInfo.getCarrierCodes()!=null){
					hql+=" or t.factory.code in ("+enddelivery+")";
				}else{
					hql+=" and (t.factory.code in ("+enddelivery+")";
				}
			}
			if(sessionInfo.getOwnerCodes()!=null){
				String enddelivery="";
				String ownerCodes=sessionInfo.getOwnerCodes();
				for (String delivery : ownerCodes.split(",")) {
					if (delivery != null && !delivery.equalsIgnoreCase("")) {
						enddelivery+="'"+delivery+"',";
					}
				}
				enddelivery=enddelivery.substring(0,enddelivery.length()-1);
				if(sessionInfo.getCarrierCodes()!=null || sessionInfo.getFactoryCodes()!=null){
					hql+=" or t.owner.code in ("+enddelivery+")";
				}else{
					hql+=" and (t.owner.code in ("+enddelivery+")";
				}
			}

		}
		return hql+")";
	}

	public String spiltcodes(Transport transport, String hql) {
		if (transport.getCodes() != null&&!"".equals(transport.getCodes())) {
			//hql += " and t.code in :code";
			String codes=transport.getCodes().trim();
			String endcode="";
			if(codes.contains("，")){
				codes=codes.replaceAll("，", ",");
			}
			for (String code : codes.split(",")) {
				if (code != null && !code.equalsIgnoreCase("")) {
			    endcode+="'"+code+"',";
				}
			}
			endcode=endcode.substring(0,endcode.length()-1);
			hql+=" and t.code in("+endcode+")";
			
		}
		if(transport.getStatus()!=null&&!"".equals(transport.getStatus())){
			String status=transport.getStatus();
			if(status.equals("NONE")){
				//客户订单先预约再发货，在途追踪只能是状态为发货、运输商收货、仓库收货才能执行。
				hql+=" and (t.status ='SHIP' or t.status ='ARRIVE')";
			}else{
				hql+=" and t.status ='"+status+"'";
			}
		}
		return hql;
	}

	private String orderHql(PageHelper ph) {
		String orderString = "";
		if (ph.getSort() != null && ph.getOrder() != null) {
			orderString = " order by t." + ph.getSort() + " " + ph.getOrder();
		}
		return orderString;
	}

	@Override
	synchronized public void add(Transport transport) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("code", transport.getCode());
		if (transportDao.count("select count(*) from Ttransport t where t.code = :code", params) > 0) {
			throw new Exception("预约编码已存在！");
		} else {
			Ttransport ttransport = new Ttransport();
			BeanUtils.copyProperties(transport, ttransport);
			transportDao.save(ttransport);
		}
	}

	@Override
	public Transport get(String id) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", id);
		Ttransport t = transportDao.get("select distinct t from Ttransport t where t.id = :id", params);
		Transport transport = new Transport();
		BeanUtils.copyProperties(t,transport);
		return transport;
	}

	@Override
	synchronized public void edit(Transport transport) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", transport.getId());
		params.put("code", transport.getCode());
		if (transportDao.count("select count(*) from Ttransport t where t.code = :code and t.id != :id", params) > 0) {
			throw new Exception("预约编码已存在！");
		} else {
			Ttransport ttransport = transportDao.get(Ttransport.class, transport.getId());
			BeanUtils.copyProperties(transport, ttransport);
		}
	}

	@Override
	public void delete(String id) {
		//删除该订单追踪信息
		List<Ttrace> tcs = traceDao.find("from Ttrace t where t.transport.id = '"+id+"'");
		if(tcs!=null && tcs.size()>0){
			for(Ttrace tc : tcs){
				traceDao.delete(tc);
			}
		}
		List<TtransportDetail> list = null;
		//删除明显
		list = transportDetailDao.find("from TtransportDetail t where t.transport.id = '"+id+"'");
		if(list!=null && list.size()>0){
			for(TtransportDetail td : list){
				transportDetailDao.delete(td);
			}
		}

		transportDao.delete(transportDao.get(Ttransport.class, id));
	}

	@Override
	public String savetransportwithExcel(MultipartFile excelFile,String name) {
		String message = "导入失败";
		Workbook book = null;
		String filetype=excelFile.getContentType();
		//获取文件类型
		//System.out.println("name："+name+"————"+"filetype："+filetype);
		// 判断文件类型
		//filetype.equals("application/vnd.ms-excel")||filetype.equals("application/ms-excel")||filetype.equals("application/x-excel")
		if (filetype.contains("excel")) {
			try {
				book = new HSSFWorkbook(excelFile.getInputStream());
			} catch (FileNotFoundException e) {
				message = "导入失败未检索到模版数据";
				e.printStackTrace();
			} catch (IOException e) {
				message = "导入失败";
				e.printStackTrace();
			}
		}else{
			return "文件类型错误！请选择excel导入！";
		}
		//获取导入模版的名字
		String excelName=excelFile.getOriginalFilename();
		// book.getNumberOfSheets(); 判断Excel文件有多少个sheet
		Sheet sheet = book.getSheetAt(0);
		ExcelWorkSheet<Ttransport> excelWorkSheet = new ExcelWorkSheet<Ttransport>();
		// 保存工作单名称
		Row firstRow = sheet.getRow(0);
		Iterator<Cell> iterator = firstRow.iterator();
		// 保存列名
		List<String> cellNames = new ArrayList<String>();
		while (iterator.hasNext()) {
			cellNames.add(iterator.next().getStringCellValue());
		}
		excelWorkSheet.setColumns(cellNames);
		String docId = "";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");

		//创建订单
		if(excelName.contains("createOrder")){
			docId = sdf.format(new Date());
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Ttransport transport = null;
				Ttransport t = null;
				List<TtransportDetail> list = null;
				String code = "";
				String type = "";
				Row ros = sheet.getRow(i);
				//设置订单号
				if(ros.getCell(0) != null && !ros.getCell(0).toString().trim().equals("")){
					ros.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
					code = ros.getCell(0).getStringCellValue();
				}else{
					return "请填写第"+(i+1)+"行订单号！";
				}
				//通过订单号判断是否存在该状态为新建的该订单
				transport = transportDao.get("from Ttransport t where t.code = '"+code+"'");
				if(transport!=null){
					if(transport.getStatus().equals("NEW")){
						//判断是不是本次导入的，不是本次导入的就删除，是本次导入的就添加明细
						if(!transport.getDocId().equals(docId)){
							//存在相同的订单，先删除，再插入
							//首先删除订单明细
							list = transportDetailDao.find("from TtransportDetail t where t.transport.id = '"+transport.getId()+"'");
							if(list!=null && list.size()>0){
								for(TtransportDetail td : list){
									transportDetailDao.delete(td);
								}
							}
							//删除订单
							transportDao.delete(transport);

							//删除成功，新建订单
							t = new Ttransport();
							t.setId(UUID.randomUUID().toString());
							t.setCode(code);
							t.setDocId(docId);
							t.setCreateTime(new Date());
							//设置订单类型（海运、空运、陆运）
							if(ros.getCell(1) != null && !ros.getCell(1).toString().trim().equals("")){
								ros.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
								type = ros.getCell(1).getStringCellValue();
							/*if(type.equals("LAND") || type.equals("AIR") || type.equals("SEA")){
								t.setType(type);
							}else{
								return "第"+(i+1)+"行订单类型错误（LAND/AIR/SEA）";
							}*/
								if(type.equals("陆运")){
									t.setType("LAND");
								}else if(type.equals("空运")){
									t.setType("ATR");
								}else if(type.equals("海运")){
									t.setType("SEA");
								}else{
									return "第"+(i+1)+"行订单类型错误（海运/陆运/空运）";
								}
							}else{
								return "请填写第"+(i+1)+"行订单类型！";
							}
							//设置货主
							if(ros.getCell(2) != null && !ros.getCell(2).toString().trim().equals("")) {
								ros.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
								String ownerCode = ros.getCell(2).getStringCellValue();
								//验证货主编码是否为合作伙伴
								Tstorer tstorer = storerDao.get("from Tstorer t where t.type = 'OWNER' and t.code = '"+ownerCode+"'");
								if(tstorer!=null){
									t.setOwner(tstorer);
								}else{
									return "第"+(i+1)+"行货主编码错误，在合作伙伴管理中没有找到或类型不是货主！";
								}
							}else{
								return "请填写第"+(i+1)+"行货主编码！";
							}

							//设置发货仓库
							if(ros.getCell(3) != null && !ros.getCell(3).toString().trim().equals("")) {
								ros.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
								String factoryCode = ros.getCell(3).getStringCellValue();
								//验证发货仓库编码是否为合作伙伴
								Tstorer tstorer = storerDao.get("from Tstorer t where t.type = 'FACTORY' and t.code = '"+factoryCode+"'");
								if(tstorer!=null){
									t.setFactory(tstorer);
								}else{
									return "第"+(i+1)+"行发货仓库编码错误，在合作伙伴管理中没有找到或类型不是发货仓库！";
								}
							}else{
								return "请填写第"+(i+1)+"行发货仓库编码！";
							}

							//设置承运商
							if(ros.getCell(4) != null && !ros.getCell(4).toString().trim().equals("")) {
								ros.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
								String carrierCode = ros.getCell(4).getStringCellValue();
								//验证发货仓库编码是否为合作伙伴
								Tstorer tstorer = storerDao.get("from Tstorer t where t.type = 'CARRIER' and t.code = '"+carrierCode+"'");
								if(tstorer!=null){
									t.setCarrier(tstorer);
								}else{
									return "第"+(i+1)+"行承运商编码错误，在合作伙伴管理中没有找到或类型不是承运商！";
								}
							}else{
								return "请填写第"+(i+1)+"行承运商编码！";
							}
							t.setStatus("NEW");
							transportDao.saveOrUpdate(t);
						}

						//插入明细
						TtransportDetail detail = null;
						//获取物料编码
						if(ros.getCell(5) != null && !ros.getCell(5).toString().trim().equals("")) {
							ros.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
							String productCode = ros.getCell(5).getStringCellValue();
							//验证物料是否为维护的产品
							Tproduct tproduct = productDao.get("from Tproduct t where t.code = '"+productCode+"'");
							if(tproduct!=null){
								//判断物料明细是否已存在，存在则更新
								detail = transportDetailDao.get("from TtransportDetail t where t.product.code = '"+productCode+"' and t.transport.id = '"+transport.getId()+"'");
								if(detail==null){
									detail = new TtransportDetail();
									detail.setId(UUID.randomUUID().toString());
									detail.setProduct(tproduct);
									if(t==null){
										detail.setTransport(transport);
									}else{
										detail.setTransport(t);
									}
								}
								//设置产品数量
								int qty = 0;
								if(ros.getCell(6) != null && !ros.getCell(6).toString().trim().equals("")) {
									ros.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
									try{
										qty = Integer.parseInt(ros.getCell(6).getStringCellValue());
									}catch (Exception e){
										return "第"+(i+1)+"行产品数量请填写数字！";
									}
									detail.setQty(qty);
									detail.setVolume(qty*detail.getProduct().getVolume());
									detail.setWeight(qty*detail.getProduct().getWeight());
								}

								transportDetailDao.saveOrUpdate(detail);

							}else{
								return "第"+(i+1)+"行物料编码错误，在产品管理中没有找到！";
							}
						}else{
							return "请填写第"+(i+1)+"行承运商编码！";
						}
					}else{
						//订单已经发货或到货，不能更新
						return "第"+(i+1)+"行订单已经发货，不能更新！";
					}
				}else{
					t = new Ttransport();
					t.setId(UUID.randomUUID().toString());
					t.setCode(code);
					t.setDocId(docId);
					t.setCreateTime(new Date());
					//设置订单类型（海运、空运、陆运）
					if(ros.getCell(1) != null && !ros.getCell(1).toString().trim().equals("")){
						ros.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
						type = ros.getCell(1).getStringCellValue();
						if(type.equals("陆运")){
							t.setType("LAND");
						}else if(type.equals("空运")){
							t.setType("ATR");
						}else if(type.equals("海运")){
							t.setType("SEA");
						}else{
							return "第"+(i+1)+"行订单类型错误（海运/陆运/空运）";
						}
					}else{
						return "请填写第"+(i+1)+"行订单类型！";
					}
					//设置货主
					if(ros.getCell(2) != null && !ros.getCell(2).toString().trim().equals("")) {
						ros.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
						String ownerCode = ros.getCell(2).getStringCellValue();
						//验证货主编码是否为合作伙伴
						Tstorer tstorer = storerDao.get("from Tstorer t where t.type = 'OWNER' and t.code = '"+ownerCode+"'");
						if(tstorer!=null){
							t.setOwner(tstorer);
						}else{
							return "第"+(i+1)+"行货主编码错误，在合作伙伴管理中没有找到或类型不是货主！";
						}
					}else{
						return "请填写第"+(i+1)+"行货主编码！";
					}

					//设置发货仓库
					if(ros.getCell(3) != null && !ros.getCell(3).toString().trim().equals("")) {
						ros.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
						String factoryCode = ros.getCell(3).getStringCellValue();
						//验证发货仓库编码是否为合作伙伴
						Tstorer tstorer = storerDao.get("from Tstorer t where t.type = 'FACTORY' and t.code = '"+factoryCode+"'");
						if(tstorer!=null){
							t.setFactory(tstorer);
						}else{
							return "第"+(i+1)+"行发货仓库编码错误，在合作伙伴管理中没有找到或类型不是发货仓库！";
						}
					}else{
						return "请填写第"+(i+1)+"行发货仓库编码！";
					}

					//设置承运商
					if(ros.getCell(4) != null && !ros.getCell(4).toString().trim().equals("")) {
						ros.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
						String carrierCode = ros.getCell(4).getStringCellValue();
						//验证发货仓库编码是否为合作伙伴
						Tstorer tstorer = storerDao.get("from Tstorer t where t.type = 'CARRIER' and t.code = '"+carrierCode+"'");
						if(tstorer!=null){
							t.setCarrier(tstorer);
						}else{
							return "第"+(i+1)+"行承运商编码错误，在合作伙伴管理中没有找到或类型不是承运商！";
						}
					}else{
						return "请填写第"+(i+1)+"行承运商编码！";
					}
					t.setStatus("NEW");
					transportDao.saveOrUpdate(t);

					//插入明细
					TtransportDetail detail = null;
					//获取物料编码
					if(ros.getCell(5) != null && !ros.getCell(5).toString().trim().equals("")) {
						ros.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
						String productCode = ros.getCell(5).getStringCellValue();
						//验证物料是否为维护的产品
						Tproduct tproduct = productDao.get("from Tproduct t where t.code = '"+productCode+"'");
						if(tproduct!=null){

							//这里一定是新建，直接插入
							detail = new TtransportDetail();
							detail.setId(UUID.randomUUID().toString());
							detail.setProduct(tproduct);
							detail.setTransport(t);

							//设置产品数量
							int qty = 0;
							if(ros.getCell(6) != null && !ros.getCell(6).toString().trim().equals("")) {
								ros.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
								try{
									qty = Integer.parseInt(ros.getCell(6).getStringCellValue());
								}catch (Exception e){
									return "第"+(i+1)+"行产品数量请填写数字！";
								}
								detail.setQty(qty);
								detail.setVolume(qty*detail.getProduct().getVolume());
								detail.setWeight(qty*detail.getProduct().getWeight());
							}
							transportDetailDao.saveOrUpdate(detail);
						}else{
							return "第"+(i+1)+"行物料编码错误，在产品管理中没有找到！";
						}
					}else{
						return "请填写第"+(i+1)+"行承运商编码！";
					}
				}
				message = "创建订单成功！";
			}
		}

		if(excelName.contains("shipOrder")){
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row ros = sheet.getRow(i);
				String code = "";
				if(ros.getCell(0)!=null && !ros.getCell(0).toString().trim().equals("")){
					ros.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
					code = ros.getCell(0).getStringCellValue();
				}else{
					return "请填写第"+(i+1)+"行订单号！";
				}
				//判断订单号是否存在
				Ttransport t = transportDao.get("from Ttransport t where t.code = '" + code + "'");
				if(t!=null){
					//存在则判断状态是否为新建或发货
					if(!t.getStatus().equals("ARRIVE")){
						//设置发货时间
						if(ros.getCell(1)!=null && !ros.getCell(1).toString().trim().equals("")){
							try{
								Date d = ros.getCell(1).getDateCellValue();
								t.setSendTime(d);
							}catch (Exception e){
								return "第"+(i+1)+"行发货时间日期格式错误！";
							}
						}else{
							return "请填写第"+(i+1)+"行发货日期！";
						}
						//设置发货标识（车牌号）
						if(ros.getCell(2)!=null && !ros.getCell(2).toString().trim().equals("")){
							ros.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
							t.setSendNo(ros.getCell(2).getStringCellValue());
						}else{
							return "请填写第"+(i+1)+"行发货标识（车牌号等）！";
						}
						//设置发货备注
						if(ros.getCell(3)!=null && !ros.getCell(3).toString().trim().equals("")){
							ros.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
							t.setSendRemark(ros.getCell(3).getStringCellValue());
						}
						//设置未发送邮件
						t.setSend(false);
						t.setStatus("SHIP");
						transportDao.update(t);

						message = "发货成功！";
					}else{
						return "第"+(i+1)+"行订单已经到货，不能进行发货！";
					}
				}else{
					return "第"+(i+1)+"行订单号不存在！";
				}
			}
		}
		if(excelName.contains("arriveOrder")){
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row ros = sheet.getRow(i);
				String code = "";
				if(ros.getCell(0)!=null && !ros.getCell(0).toString().trim().equals("")){
					ros.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
					code = ros.getCell(0).getStringCellValue();
				}else{
					return "请填写第"+(i+1)+"行订单号！";
				}
				//判断订单号是否存在
				Ttransport t = transportDao.get("from Ttransport t where t.code = '" + code + "'");
				if(t!=null){
					//存在则判断状态是否为新建或发货
					if(!t.getStatus().equals("NEW")){
						//设置发货时间
						if(ros.getCell(1)!=null && !ros.getCell(1).toString().trim().equals("")){
							try{
								Date d = ros.getCell(1).getDateCellValue();
								t.setReceiveTime(d);
							}catch (Exception e){
								return "第"+(i+1)+"行到货时间日期格式错误！";
							}
						}else{
							return "请填写第"+(i+1)+"行到货日期！";
						}
						//设置发货标识（车牌号）
						if(ros.getCell(2)!=null && !ros.getCell(2).toString().trim().equals("")){
							ros.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
							t.setReceiveType(ros.getCell(2).getStringCellValue());
						}else{
							return "请填写第"+(i+1)+"行到货方式！";
						}
						//设置发货备注
						if(ros.getCell(3)!=null && !ros.getCell(3).toString().trim().equals("")){
							ros.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
							t.setReceiveRemark(ros.getCell(3).getStringCellValue());
						}
						t.setStatus("ARRIVE");
						transportDao.update(t);

						message = "到货成功！";
					}else{
						return "第"+(i+1)+"行订单未发货，不能进行到货操作！";
					}
				}else{
					return "第"+(i+1)+"行订单号不存在！";
				}
			}
		}

		return message;
	}

	@Override
	public Ttransport getbyId(String id) {
	Ttransport transport=transportDao.get(Ttransport.class,id);
	return transport;
	}

	@Override
	public void send() {
		String hql="from Ttransport t where t.status = 'SHIP' and t.send = '0'";
		List<Ttransport> transports=transportDao.find(hql);

		//用来记录以owner为key，所有状态为发货且没有发送邮件的订单中所有货主为这个值的订单为value的集合
		Map<String,List<Ttransport>> map = new HashMap<String,List<Ttransport>>();
		List<Ttransport> valueList = null;
		if(transports!=null && transports.size()>0){
			//在未发送邮件的订单根据货主分类
			for(int i=0;i<transports.size();i++){
				//每次循环都新建一个集合
				valueList = new ArrayList<Ttransport>();
				//将第一个订单加入集合中
				valueList.add(transports.get(i));
				//从第二个订单开始遍历所有订单，看owner是否有相同的，如果有相同的就加入到新集合中，然后在原集合中删除
				for(int j=i+1;j<transports.size();j++){
					if(transports.get(i).getOwner().getId().equals(transports.get(j).getOwner().getId())){
						valueList.add(transports.get(j));
						transports.remove(j);
						j-=1;
					}
				}
				map.put(transports.get(i).getOwner().getId(), valueList);
			}
			//对于map中所有的key依次做处理
			//遍历所有的key
			for(String shipto: map.keySet()){
				List<Transport> list = new ArrayList<Transport>();
				//取出对应的集合
				List<Ttransport> emls = map.get(shipto);
				for(Ttransport t : emls ){
					Ttransport transport = transportDao.get("from Ttransport t where t.id ='"+t.getId()+"'");
					if(transport !=null){
						t.setSend(true);
						transportDao.update(t);
						Transport tr = new Transport();
						BeanUtils.copyProperties(transport, tr);
						tr.setOwnerName(transport.getOwner().getName());
						tr.setCarrierName(transport.getCarrier().getName());
						tr.setFactoryName(transport.getFactory().getName());
						list.add(tr);
					}
				}
				//导出这次要发送邮件的文件
				String path= "";
				if(list != null && list.size()>0){
					try {
						path = rejectionExcelExport(list);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				//获取邮箱管理中所有所属shipto为shipto的联系人，其邮箱地址就是要发送邮件的地址
				List<String> receives=new ArrayList<String>();
				Tstorer l =storerDao.get("from Tstorer t where t.id ='"+shipto+"'");
				receives.add(l.getEmail().trim());
				//如果联系人列表为空，不用发送邮件
				if(receives!=null){
					String rcvs = "";
					for(String rcv : receives){
						rcvs+=rcv+",";
					}

					//如果path没有被赋值，则说明list中没有值，既订单表中没有该条订单记录，这时不用发送邮件。
					if(path != null && !path.equals("")){
						try {
							EmailUtil.send("sinotrans@jcmos.com", receives, "订单发货信息邮件", "下载附件查看发货信息，详情请直接登录系统查看，本邮箱为系统邮箱，请勿直接回复！", path);
							logger.info("发送成功！收件人："+rcvs+";邮件描述：订单发货信息邮件.");
							File file = new File(path);
							if (!file.exists()) {
								//				System.out.println("发送文件失败:"+path+"不存在！");
							}  else{
								file.delete();
								//System.out.println("发送邮件成功");
							}
						} catch (AddressException e) {
							logger.error("发送失败！收件人："+rcvs+";邮件描述：订单发货信息邮件.AddressException:"+e.getMessage());
						} catch (MessagingException e) {
							logger.error("发送失败！收件人："+rcvs+";邮件描述：订单发货信息邮件.MessagingException:"+e.getMessage());
						}
					}
				}
			}
		}else{
			return;
		}



	}
	public String rejectionExcelExport(List<Transport> transports) throws Exception {


		//	List<Order> list = orderService.rejectionExcelExport(order);// 获取数据
		SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
		String formatdate = format.format(new Date());
		String title = "sendOrder" + formatdate;
		String creator = "";
		String path=System.getProperty("user.dir")+System.getProperty("file.separator")+title+""+".xls";

		creator = "李晨豪";
		String[] hearders = null;
		String[] fields = null;

		hearders = new String[] { "订单号","货主", "发货仓库", "运输商", "发货日期", "发货备注" };// 表头数组

		fields = new String[] { "code", "ownerName" ,"factoryName","carrierName","sendTime","sendRemark" };// 订单对象属性数组



		TableData td = ExcelUtils.createTableData(transports, ExcelUtils.createTableHeader(hearders), fields);
		JsGridReportBase report = new JsGridReportBase();
		report.exportToExcelForEmail(title, creator, td,path);
		transports = null;
		return path;
	}

	@Override
	public DataGrid tracedataGrid(Transport transport, PageHelper ph, SessionInfo sessionInfo) {
		DataGrid dg = new DataGrid();
		List<Transport> ul = new ArrayList<Transport>();
		Map<String, Object> params = new HashMap<String, Object>();
		String hql = " from Ttransport t ";
		List<Ttransport> l = transportDao.find(hql + whereHql(transport, params, sessionInfo) + orderHql(ph), params, ph.getPage(), ph.getRows());
		if (l != null && l.size() > 0) {
			for (Ttransport t : l) {
				Transport u = new Transport();
				BeanUtils.copyProperties(t, u);
				ul.add(u);
			}
		}
		dg.setRows(ul);
		dg.setTotal(transportDao.count("select count(*) " + hql + whereHql(transport, params,sessionInfo), params));
		return dg;
	}


//	@Override
//	public List<Transport> exporttransportsExcel(Transport transport) {
//		List<Ttransport> ttransports=null;
//		List<Transport> transports=null;
//		String hql="  from Ttransport t where 1=1 and t.transport is null and t.status in('ADD','BOOKING')  ";
//		if(transport.getCodes()!=null){
//			hql = spiltcodes(transport, hql);
//		}
//		ttransports=transportDao.find(hql);
//		transports= changeTtoM(ttransports);
//
//
//		return transports;
//	}

//public List<Transport> changeTtoM(List<Ttransport> ttransports){
//	List<Transport> transports=null;
//	if (ttransports != null && ttransports.size() > 0) {
//		transports=new ArrayList<Transport>();
//		for (Ttransport t : ttransports) {
//			Transport transport = new Transport();
//			BeanUtils.copyProperties(t, transport);
//			//设置承运商
//			Tstorer carrier=null;
//			carrier=t.getCarrier();
//			if (carrier != null ) {
//			transport.setCarrierId(carrier.getId());
//			transport.setCarrierName(carrier.getName());
//			transport.setCarrierCode(carrier.getCode());
//			}
//			//设置货主
//			Tstorer owner=null;
//			owner=t.getOwner();
//			if (owner != null ) {
//			transport.setOwnerId(owner.getId());
//			transport.setOwnerName(owner.getName());
//			transport.setOwnerCode(owner.getCode());
//			}
//
//			//设置发货方
//			Tstorer sender=null;
//			sender=t.getSender();
//			if (sender != null ) {
//			transport.setSenderId(sender.getId());
//		    transport.setSenderName(sender.getName());
//			transport.setSenderCode(sender.getCode());
//		    }
//			//设置收货方
//			Tstorer receiver=null;
//			receiver=t.getReceiver();
//			if (receiver != null ) {
//				transport.setReceiverId(receiver.getId());
//				transport.setReceiverName(receiver.getName());
//				transport.setReceiverCode(receiver.getCode());
//			}
//			//设置产品
//			Tproduct product=null;
//			product=t.getProduct();
//			if (product != null ) {
//				transport.setProductId(product.getId());
//				transport.setProductName(product.getName());
//				transport.setProductCode(product.getCode());
//			}
//			//如果有父记录则设置父记录
//			if(t.getTransport()!=null){
//				transport.setpId(t.getTransport().getId());
//			}
//
//			transports.add(transport);
//		}
//	}
//	return transports;
//
//}


}
